const Database = require('better-sqlite3');
const fs = require('fs');
const path = require('path');

// 配置
const DB_PATH = './data/delivery-management.db';
const BACKUP_DIR = './sql/backup';
const SCHEMA_DIR = './sql/schema';
const DATA_DIR = './sql/data';

// 创建目录
function ensureDir(dir) {
  if (!fs.existsSync(dir)) {
    fs.mkdirSync(dir, { recursive: true });
  }
}

// 导出数据库备份
function backupDatabase() {
  try {
    console.log('=== 交付管理系统数据库备份脚本 ===');
    console.log('Delivery Management System Database Backup Script');
    console.log('');

    // 检查数据库文件是否存在
    if (!fs.existsSync(DB_PATH)) {
      console.error(`错误: 数据库文件 ${DB_PATH} 不存在`);
      return;
    }

    // 创建目录
    ensureDir(BACKUP_DIR);
    ensureDir(SCHEMA_DIR);
    ensureDir(DATA_DIR);

    console.log('开始备份数据库...');

    const db = new Database(DB_PATH);

    // 1. 导出数据库结构
    console.log('导出数据库结构...');
    const tables = db.prepare('SELECT name, sql FROM sqlite_master WHERE type=? ORDER BY name').all('table');
    
    let schemaContent = '';
    tables.forEach(table => {
      if (table.sql) {
        schemaContent += table.sql + ';\n\n';
      }
    });
    
    fs.writeFileSync(path.join(SCHEMA_DIR, 'schema.sql'), schemaContent);

    // 2. 导出完整备份 (模拟 .dump 功能)
    console.log('导出完整数据库备份...');
    let fullBackup = '-- 数据库备份文件\n-- Database Backup File\n\n';
    
    // 添加结构
    fullBackup += schemaContent;
    
    // 添加数据
    tables.forEach(table => {
      try {
        const rows = db.prepare(`SELECT * FROM ${table.name}`).all();
        if (rows.length > 0) {
          fullBackup += `\n-- 数据表: ${table.name}\n`;
          fullBackup += `-- Table data: ${table.name}\n`;
          
          rows.forEach(row => {
            const columns = Object.keys(row);
            const values = columns.map(col => {
              const value = row[col];
              if (value === null) return 'NULL';
              if (typeof value === 'string') return `'${value.replace(/'/g, "''")}'`;
              return value;
            });
            
            fullBackup += `INSERT INTO ${table.name} (${columns.join(', ')}) VALUES (${values.join(', ')});\n`;
          });
        }
      } catch (error) {
        console.log(`警告: 无法导出表 ${table.name} 的数据: ${error.message}`);
      }
    });
    
    fs.writeFileSync(path.join(BACKUP_DIR, 'full-backup.sql'), fullBackup);

    // 3. 导出核心表数据
    console.log('导出用户和角色数据...');
    
    // 导出角色数据
    try {
      const roles = db.prepare('SELECT * FROM roles').all();
      let rolesContent = '';
      roles.forEach(role => {
        const values = [
          role.id ? `'${role.id}'` : 'NULL',
          role.code ? `'${role.code}'` : 'NULL',
          role.name ? `'${role.name}'` : 'NULL',
          role.description ? `'${role.description}'` : 'NULL',
          role.permissions ? `'${role.permissions}'` : 'NULL',
          role.created_at ? `'${role.created_at}'` : 'NULL',
          role.updated_at ? `'${role.updated_at}'` : 'NULL'
        ];
        rolesContent += `INSERT INTO roles VALUES(${values.join(',')});\n`;
      });
      fs.writeFileSync(path.join(DATA_DIR, 'roles.sql'), rolesContent);
    } catch (error) {
      console.log(`警告: 无法导出角色数据: ${error.message}`);
    }

    // 导出用户数据
    try {
      const users = db.prepare('SELECT * FROM users').all();
      let usersContent = '';
      users.forEach(user => {
        const values = [
          user.id ? `'${user.id}'` : 'NULL',
          user.username ? `'${user.username}'` : 'NULL',
          user.password ? `'${user.password}'` : 'NULL',
          user.email ? `'${user.email}'` : 'NULL',
          user.full_name ? `'${user.full_name}'` : 'NULL',
          user.role_id ? `'${user.role_id}'` : 'NULL',
          user.is_active !== undefined ? user.is_active : 'NULL',
          user.created_at ? `'${user.created_at}'` : 'NULL',
          user.updated_at ? `'${user.updated_at}'` : 'NULL'
        ];
        usersContent += `INSERT INTO users VALUES(${values.join(',')});\n`;
      });
      fs.writeFileSync(path.join(DATA_DIR, 'users.sql'), usersContent);
    } catch (error) {
      console.log(`警告: 无法导出用户数据: ${error.message}`);
    }

    // 导出设备数据
    try {
      console.log('导出设备数据...');
      const devices = db.prepare('SELECT * FROM devices').all();
      let devicesContent = '';
      devices.forEach(device => {
        const values = Object.keys(device).map(key => {
          const value = device[key];
          if (value === null) return 'NULL';
          if (typeof value === 'string') return `'${value.replace(/'/g, "''")}'`;
          return value;
        });
        devicesContent += `INSERT INTO devices VALUES(${values.join(',')});\n`;
      });
      fs.writeFileSync(path.join(DATA_DIR, 'devices.sql'), devicesContent);
    } catch (error) {
      console.log(`警告: 无法导出设备数据: ${error.message}`);
    }

    // 导出参数元数据
    try {
      console.log('导出参数元数据...');
      const fieldMetadata = db.prepare('SELECT * FROM parameter_field_metadata').all();
      let fieldContent = '';
      fieldMetadata.forEach(field => {
        const values = Object.keys(field).map(key => {
          const value = field[key];
          if (value === null) return 'NULL';
          if (typeof value === 'string') return `'${value.replace(/'/g, "''")}'`;
          return value;
        });
        fieldContent += `INSERT INTO parameter_field_metadata VALUES(${values.join(',')});\n`;
      });
      fs.writeFileSync(path.join(DATA_DIR, 'parameter_field_metadata.sql'), fieldContent);

      const tableMetadata = db.prepare('SELECT * FROM parameter_table_metadata').all();
      let tableContent = '';
      tableMetadata.forEach(table => {
        const values = Object.keys(table).map(key => {
          const value = table[key];
          if (value === null) return 'NULL';
          if (typeof value === 'string') return `'${value.replace(/'/g, "''")}'`;
          return value;
        });
        tableContent += `INSERT INTO parameter_table_metadata VALUES(${values.join(',')});\n`;
      });
      fs.writeFileSync(path.join(DATA_DIR, 'parameter_table_metadata.sql'), tableContent);
    } catch (error) {
      console.log(`警告: 无法导出参数元数据: ${error.message}`);
    }

    db.close();

    console.log('');
    console.log('备份完成！');
    console.log('');

    // 显示备份统计信息
    console.log('备份统计信息:');
    const fullBackupStats = fs.statSync(path.join(BACKUP_DIR, 'full-backup.sql'));
    console.log(`完整备份文件: ${Math.round(fullBackupStats.size / 1024)}KB`);
    
    const schemaStats = fs.statSync(path.join(SCHEMA_DIR, 'schema.sql'));
    console.log(`结构文件: ${Math.round(schemaStats.size / 1024)}KB`);
    
    console.log('');
    console.log('数据文件:');
    const dataFiles = fs.readdirSync(DATA_DIR).filter(file => file.endsWith('.sql'));
    dataFiles.forEach(file => {
      const filePath = path.join(DATA_DIR, file);
      const stats = fs.statSync(filePath);
      const content = fs.readFileSync(filePath, 'utf8');
      const lines = content.split('\n').length;
      console.log(`  ${file}: ${Math.round(stats.size / 1024)}KB (${lines} 行)`);
    });

    console.log('');
    console.log('所有备份文件已生成完成！');

  } catch (error) {
    console.error('备份过程中发生错误:', error.message);
    process.exit(1);
  }
}

// 运行备份
backupDatabase();